package model;

import java.io.IOException;
import java.io.FileInputStream;

import java.io.InputStream;
import java.util.ArrayList;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;

import controller.Controller;

public class Excel_Input {

	private OPCPackage pack = null;
	private XSSFWorkbook workbook = null;

	/**
	 * @param filename
	 *            Der Dateipfad der Exceldatei, die gelesen werden soll.
	 */
	public Excel_Input(String filename) {
		try {
			InputStream inputStream = new FileInputStream(filename);
			this.pack = OPCPackage.open(inputStream);
			this.workbook = new XSSFWorkbook(pack);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<Job> importJobListFromExcel(){
		ArrayList<Job> list = new ArrayList<Job>();
		String[][] input = readSheet(0, 0, 1, 4, 102);
		
		for (int i = 1; i < input.length; i++) {
			list.add(new Job(input[i][0], input[i][2], input[i][1], input[i][3]));
		}
		
		return list;
	}
	
	public void importJobListIntoDatabase(Controller controller){
		controller.getSql().insertNewJobListIntoDatabase(importJobListFromExcel());
	}

	private String[][] readSheet(int sheetNumber, int startColumn, int startRow,
			int maxColumn, int maxRow) {
		String[][] result = new String[maxRow][maxColumn];
		XSSFSheet sheet = this.workbook.getSheetAt(sheetNumber);
		for (int i = startRow; i < maxRow; i++) {
			XSSFRow row = sheet.getRow(i);
			for (int j = startColumn; j < maxColumn; j++) {
				XSSFCell cell = row.getCell(j);
				result[i][j] = getCellResultAsString(cell);
			}
		}

		return result;
	}

	private String getCellResultAsString(XSSFCell cell) {
		String result = "";
		switch (cell.getCellType()) {
		case XSSFCell.CELL_TYPE_NUMERIC: {
			// Cell Type Zahl
			result += cell.getNumericCellValue();
			break;
		}

		case XSSFCell.CELL_TYPE_STRING: {
			// Cell Type String
			XSSFRichTextString richTextString = cell.getRichStringCellValue();

			result = richTextString.getString();

			break;
		}

		default: {
			break;
		}
		}
		return result;
	}
	public void close(){
		try {
			this.pack.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
